/* AirSortReportExcel.java
 *
 * Project/Product: GWBS2009
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   apep	2010-4-16	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2010
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package com.apep.jwcs.service;

import java.util.Collection;
import java.util.Iterator;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import com.apep.jwcs.pojo.AirSortReport;

public class AirSortReportExcel {
    public void writeExcel_ReportAirSort(Map map,String sheetName, String month,String title, String warehouseCode,String fileName,String start,String end,HSSFWorkbook tWb){
        write_All(map, sheetName, month, title, warehouseCode, fileName, start, end,tWb);
    }

    private void write_All(Map map,String sheetName, String month,String title, String warehouseCode,String fileName,String start,String end,HSSFWorkbook tWb) {
        /** 创建通用EXCEL     开始*/
        HSSFSheet tSheet = null;
        HSSFRow tRow = null;
        Region region = null;
        HSSFCell c0 = null;
        HSSFCell c1 = null;
        HSSFCell c2 = null;
        HSSFCell c3 = null;
        HSSFCell c4 = null;
        HSSFCell c5 = null;
        HSSFCell c6 = null;
        HSSFCell c7 = null;
        HSSFFont font = tWb.createFont();
        font.setFontHeightInPoints((short) 9);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        HSSFCellStyle cStyle = tWb.createCellStyle();
        cStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        HSSFCellStyle cStyleM = tWb.createCellStyle();
        cStyleM.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cStyleM.setFont(font);
        HSSFCellStyle cStyleM_R = tWb.createCellStyle();
        cStyleM_R.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        cStyleM_R.setFont(font);
        /** 创建通用EXCEL     结束*/
        
        /** 创建Sheet 开始*/
        short index = 3; //当前所在行号 
//        double totalExpendSex = 0;//一个sheet中用来统计合计
//        double totalExpendSixOutside = 0;//一个sheet中用来统计合计
//        double totalExpandThree = 0;//一个sheet中用来统计合计
//        double totalExpandThreeInSort = 0;//一个sheet中用来统计合计
//        double totalExpandThreeNoSort = 0;//一个sheet中用来统计合计
//        double totalExpandThreeOutside= 0;//一个sheet中用来统计合计
//        double totalExpandQuickWearPart = 0;//一个sheet中用来统计合计
        double totalColumn=0;
        
        int sheetIndex = 0;//sheet创建所需要的索引
        tSheet = tWb.createSheet();
        tWb.setSheetName(sheetIndex,"仓库系统", HSSFCell.ENCODING_UTF_16);
        tSheet.setColumnWidth((short) 0, (short) (12 * 256));
        tSheet.setColumnWidth((short) 1, (short) (12 * 256));
        tSheet.setColumnWidth((short) 2, (short) (15 * 256));
        tSheet.setColumnWidth((short) 3, (short) (15 * 256));
        tSheet.setColumnWidth((short) 4, (short) (15 * 256));
        tSheet.setColumnWidth((short) 5, (short) (15 * 256));
        tSheet.setColumnWidth((short) 6, (short) (12 * 256));
        tSheet.setColumnWidth((short) 7, (short) (12 * 256));
        tSheet.setColumnWidth((short) 8, (short) (12 * 256));
        //标题行
        tRow = tSheet.createRow((short) 0);
        c0 = createCell(tWb, tRow, (short) 5, sheetName);
        c0.setCellStyle(cStyleM);
        //日期行
        tRow = tSheet.createRow((short) 1);
        region = new Region(1, (short) 2, 1, (short) 13);
        tSheet.addMergedRegion(region);
        createCell(tWb, tRow, (short) 2, "仓库:" + "仓库系统" + "     " + start + " 至 " + end, HSSFCellStyle.ALIGN_CENTER, false);
        //表头行
        tRow = tSheet.createRow((short) 2);
        tSheet.addMergedRegion(new Region(2, (short) 0, 3, (short) 0));
        c1 = createCell(tWb, tRow, (short) 0, "品牌");
        c1.setCellStyle(createTopTitleStyle(tWb));
        
        tSheet.addMergedRegion(new Region(2,(short)1,2,(short)2));
        HSSFCell cSix = createCell(tWb, tRow, (short) 1, "六年质保");
        cSix.setCellStyle(createTopTitleStyle(tWb));
        
        tSheet.addMergedRegion(new Region(2,(short)3,2,(short)6));
        HSSFCell cThree = createCell(tWb, tRow, (short) 3, "3+3质保");
        cThree.setCellStyle(createTopTitleStyle(tWb));
        
        tSheet.addMergedRegion(new Region(2,(short)7,3,(short)7));
        HSSFCell cQuickWearPart = createCell(tWb, tRow, (short) 7, "易损件");
        cQuickWearPart.setCellStyle(createTopTitleStyle(tWb));
        
        tSheet.addMergedRegion(new Region(2,(short)8,3,(short)8));
        HSSFCell cAllMoney = createCell(tWb, tRow, (short) 8, "合计(需付)");
        cAllMoney.setCellStyle(createTopTitleStyle(tWb));
        
        tRow = tSheet.createRow((short) 3);
        c2 = createCell(tWb, tRow, (short)1, "质保内");
        c2.setCellStyle(createTopTitleStyle(tWb));
        c3 = createCell(tWb, tRow, (short)2, "质保外");
        c3.setCellStyle(createTopTitleStyle(tWb));
        c4 = createCell(tWb, tRow, (short)3, "3年内");
        c4.setCellStyle(createTopTitleStyle(tWb));
        c5 = createCell(tWb, tRow, (short)4, "3-6年五大类");
        c5.setCellStyle(createTopTitleStyle(tWb));
        c6 = createCell(tWb, tRow, (short)5, "3-6年非五大类");
        c6.setCellStyle(createTopTitleStyle(tWb));
        c7 = createCell(tWb, tRow, (short)6, "6年质保外");
        c7.setCellStyle(createTopTitleStyle(tWb));
        
        //空调材料自付报表
        HSSFSheet tSheet2 = tWb.createSheet();
        tWb.setSheetName(1,"空调材料自付", HSSFCell.ENCODING_UTF_16);
        tSheet2.setColumnWidth((short) 0, (short) (12 * 256));
        tSheet2.setColumnWidth((short) 1, (short) (12 * 256));
        tSheet2.setColumnWidth((short) 2, (short) (15 * 256));
        tSheet2.addMergedRegion(new Region(2, (short) 0, 2, (short) 3));
        HSSFRow tRow2 = tSheet2.createRow((short) 2);
        HSSFCell cTop = createCell(tWb, tRow2, (short) 0, "空调材料自付");
        cTop.setCellStyle(createTopTitleStyle(tWb));
        tRow2=tSheet2.createRow((short)3);
        HSSFCell c24 = createCell(tWb, tRow2, (short) 0, "品牌");
        c24.setCellStyle(createTopTitleStyle(tWb));
        HSSFCell c21 = createCell(tWb, tRow2, (short) 1, "五大类");
        c21.setCellStyle(createTopTitleStyle(tWb));
        HSSFCell c22 = createCell(tWb, tRow2, (short) 2, "非五大类");
        c22.setCellStyle(createTopTitleStyle(tWb));
        HSSFCell c23 = createCell(tWb, tRow2, (short) 3, "易损件");
        c23.setCellStyle(createTopTitleStyle(tWb));
        short index2 =  3 ;
        
        //空调材料索赔
        HSSFSheet tSheet3 = tWb.createSheet();
        tWb.setSheetName(2,"空调材料索赔", HSSFCell.ENCODING_UTF_16);
        tSheet3.setColumnWidth((short) 0, (short) (12 * 256));
        tSheet3.setColumnWidth((short) 1, (short) (12 * 256));
        tSheet3.setColumnWidth((short) 2, (short) (15 * 256));
        tSheet3.addMergedRegion(new Region(2, (short) 0, 2, (short) 3));
        HSSFRow tRow3 = tSheet3.createRow((short) 2);
        HSSFCell cTop3 = createCell(tWb, tRow3, (short) 0, "空调材料索赔");
        cTop3.setCellStyle(createTopTitleStyle(tWb));
        tRow3=tSheet3.createRow((short)3);
        HSSFCell c34 = createCell(tWb, tRow3, (short) 0, "品牌");
        c34.setCellStyle(createTopTitleStyle(tWb));
        HSSFCell c31 = createCell(tWb, tRow3, (short) 1, "五大类");
        c31.setCellStyle(createTopTitleStyle(tWb));
        HSSFCell c32 = createCell(tWb, tRow3, (short) 2, "非五大类");
        c32.setCellStyle(createTopTitleStyle(tWb));
        HSSFCell c33 = createCell(tWb, tRow3, (short) 3, "易损件");
        c33.setCellStyle(createTopTitleStyle(tWb));
        short index3 =  3 ;
        
        Collection values= map.values();
        if(map.size()>0){
            Iterator iterator = values.iterator();
            while(iterator.hasNext()){
                AirSortReport airReport=(AirSortReport) iterator.next();
                /** 为单元格填充相应数据*/
                tRow = tSheet.createRow((short) (index + 1));
                double allMoney=airReport.getAllMoney();
                createCell(tWb, tRow, (short) 0,airReport.getProductBrand()==null?"":airReport.getProductBrand());
                createCell(tWb, tRow, (short) 1,airReport.getExpendSix()==0?"0":String.valueOf(airReport.getExpendSix()+" (0)"));
                createCell(tWb, tRow, (short) 2,airReport.getExpandSixOutside() == 0 ?"0":String.valueOf(airReport.getExpandSixOutside()));
                createCell(tWb, tRow, (short) 3,airReport.getExpandThree()==0?"0":String.valueOf(airReport.getExpandThree()+ " (0)"));               
                createCell(tWb, tRow, (short) 4,airReport.getExpandThreeInSort()==0?"0":String.valueOf(airReport.getExpandThreeInSort()/0.2+"("+airReport.getExpandThreeInSort()+")"));
                createCell(tWb, tRow, (short) 5,airReport.getExpandThreeNoSort()==0?"0":String.valueOf(airReport.getExpandThreeNoSort()));
                createCell(tWb, tRow, (short) 6,airReport.getExpandThreeOutside()==0?"0":String.valueOf(airReport.getExpandThreeOutside()));
                createCell(tWb, tRow, (short) 7,airReport.getExpandQuickWearPart()==0?"0":String.valueOf(airReport.getExpandQuickWearPart()));
                createCell(tWb, tRow, (short) 8,allMoney==0?"0":String.valueOf(allMoney));
                
                /** 为单元格填充相应数据*/
                
                /** 累加本行数据  */
                index++;           
//                totalExpandQuickWearPart +=airReport.getExpandQuickWearPart() ;
//                totalExpandThree +=airReport.getExpandThree();
//                totalExpandThreeInSort +=airReport.getExpandThreeInSort();
//                totalExpandThreeNoSort +=airReport.getExpandThreeNoSort();
//                totalExpandThreeOutside+=airReport.getExpandThreeOutside();
//                totalExpendSex +=airReport.getExpendSix();
//                totalExpendSixOutside +=airReport.getExpandSixOutside();
                totalColumn +=allMoney;
                /** 累加本行数据  */
                
                tRow2 = tSheet2.createRow((short) (index2 + 1));
                createCell(tWb, tRow2, (short) 0,airReport.getProductBrand()==null?"0":airReport.getProductBrand());
                createCell(tWb, tRow2, (short) 1,airReport.getExpandFiveTypeChange()==0?"0":String.valueOf(airReport.getExpandFiveType()));
                createCell(tWb, tRow2, (short) 2,airReport.getExpandNoFiveTypeChange()==0?"0":String.valueOf(airReport.getExpandNoFiveType()));
                createCell(tWb, tRow2, (short) 3,airReport.getExpandQuickWearPart()==0?"0":String.valueOf(airReport.getExpandQuickWearPart()));
                index2++;
                
                tRow3 = tSheet3.createRow((short) (index3 + 1));
                createCell(tWb, tRow3, (short) 0,airReport.getProductBrand()==null?"":airReport.getProductBrand());
                createCell(tWb, tRow3, (short) 1,String.valueOf(airReport.getExpandFiveType()-airReport.getExpandFiveTypeChange()));
                createCell(tWb, tRow3, (short) 2,String.valueOf(airReport.getExpandNoFiveType()-airReport.getExpandNoFiveTypeChange()));
                createCell(tWb, tRow3, (short) 3,"0");
                index3++;
              
            }
        }

    }
    
    private HSSFCellStyle createTopTitleStyle(HSSFWorkbook wb){
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 9);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        HSSFCellStyle cellstyle = wb.createCellStyle();
        cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直   
        cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平   
        cellstyle.setFont(font);
        return cellstyle;
    }
    
    private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column, String alaValueue) {
        HSSFCell tCell = aRow.createCell(column);
        tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
        tCell.setCellValue(alaValueue);
        return tCell;
    }
    
    private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column, String aValue, short aAlign, boolean border) {
        HSSFCell tCell = aRow.createCell(column);
        tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
        tCell.setCellValue(aValue);
        HSSFCellStyle tCellStyle = aWb.createCellStyle();
        tCellStyle.setAlignment(aAlign);
        tCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        if (border) {
            tCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            tCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            tCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            tCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            tCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            tCellStyle.setTopBorderColor(HSSFColor.BLACK.index);
            tCellStyle.setRightBorderColor(HSSFColor.BLACK.index);
            tCellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        }
        tCell.setCellStyle(tCellStyle);
        return tCell;
    }
        
}
